/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package servidor.bbdd.subsistema3;

import beans.Administrador;
import beans.Estudiant;
import beans.ResponsableEvents;
import beans.Usuari;
import beans.Prova;
import common.EOlympicsException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import servidor.bbdd.GestorDiscoConexion;

/**
 *
 * @author Roger Guasch Rion
 */
public class GestorSubsistema3 {

    
    private GestorDiscoConexion gestorBD;
    //String paraula = new String();
    private Vector totalInscrits = new Vector();

    public GestorSubsistema3(GestorDiscoConexion pGestor){
        this.gestorBD = pGestor;
    }
    
    
    
    /**
     * Funcio que torna el numero total de proves al sistema
     * @return Numero de proves al sistema
     */
    public String getNumProves(){
        
    System.out.println("Consulta NUMERO TOTAL PROVES");
 	PreparedStatement st = null;
 	ResultSet rs = null;
        Integer numProves = null;
        //String stringNumProves = null;
        
        try { 
            StringBuffer query = new StringBuffer();
            query.append("select count(*) num_proves");
            query.append(" from prova");
            st = gestorBD.getConexion().prepareStatement(query.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);           
            rs = st.executeQuery();
            if (rs.next()) {
                numProves = new Integer(rs.getInt("num_proves"));
            }
            
        }catch (SQLException e) {
            System.out.println("Excepció SQL (funcio: getNumProves)");
 		e.printStackTrace();
                return null;
        
        }catch (Exception e){
            System.out.println("Excepció GENERAL (funcio: getNumProves)");
 		e.printStackTrace();
                return null;
        
        }
        
        return String.valueOf(numProves);

    }
    
    public String getNumProvesCurs (String estatPrevi, String estatFinal){
    
       System.out.println("Consulta NUMERO TOTAL PROVES EN CURS");
 	PreparedStatement st = null;
 	ResultSet rs = null;
        Integer numProvesEnCurs = null;
        
        try { 
            StringBuffer query = new StringBuffer();
            query.append("select count(*) numProvesEnCurs");
            query.append(" from prova");
            query.append(" where prova.estat = ? OR prova.estat = ?");
            st = gestorBD.getConexion().prepareStatement(query.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);           
            st.setString(1,estatPrevi);
            st.setString(2,estatFinal);
            rs = st.executeQuery();
            if (rs.next()) {
                numProvesEnCurs = new Integer(rs.getInt("numProvesEnCurs"));
            }
        
        
        }catch (SQLException e) {
            System.out.println("Excepció SQL (funcio: getNumProvesFinalitzades)");
 		e.printStackTrace();
                return null;
        
        }catch (Exception e){
            System.out.println("Excepció GENERAL (funcio: getNumProvesFinalitzades)");
 		e.printStackTrace();
                return null;
        }
            
            
        return String.valueOf(numProvesEnCurs);
    }
    
    public String getNumProvesFinalitzades (String estatProva){
        
        System.out.println("Consulta NUMERO TOTAL PROVES FINALITZADES");
 	PreparedStatement st = null;
 	ResultSet rs = null;
        Integer numProvesFinalitzades = null;
        
        try { 
            StringBuffer query = new StringBuffer();
            query.append("select count(*) numProvesFinalitzades");
            query.append(" from prova");
            query.append(" where prova.estat = ?");
            st = gestorBD.getConexion().prepareStatement(query.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);           
            st.setString(1,estatProva);            
            rs = st.executeQuery();
            if (rs.next()) {
                numProvesFinalitzades = new Integer(rs.getInt("numProvesFinalitzades"));
            }
        
        
        }catch (SQLException e) {
            System.out.println("Excepció SQL (funcio: getNumProvesFinalitzades)");
 		e.printStackTrace();
                return null;
        
        }catch (Exception e){
            System.out.println("Excepció GENERAL (funcio: getNumProvesFinalitzades)");
 		e.printStackTrace();
                return null;
        }
            
            
        return String.valueOf(numProvesFinalitzades);
        
    }
    
    public String getNumProvesInscrites( String estatProva){
        
        System.out.println("Consulta NUMERO TOTAL PROVES INSCRITES");
 	PreparedStatement st = null;
 	ResultSet rs = null;
        Integer numProvesInscrites = null;
        
        try { 
            StringBuffer query = new StringBuffer();
            query.append("select count(*) numProvesInscrites");
            query.append(" from prova");
            query.append(" where prova.estat = ?");
            st = gestorBD.getConexion().prepareStatement(query.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);           
            st.setString(1,estatProva);            
            rs = st.executeQuery();
            if (rs.next()) {
                numProvesInscrites = new Integer(rs.getInt("numProvesInscrites"));
            }
        
        
        }catch (SQLException e) {
            System.out.println("Excepció SQL (funcio: getNumProvesInscrites)");
 		e.printStackTrace();
                return null;
        
        }catch (Exception e){
            System.out.println("Excepció GENERAL (funcio: getNumProvesInscrites)");
 		e.printStackTrace();
                return null;
        }
            
            
        return String.valueOf(numProvesInscrites);
        
    }
    /*
    public String getMitjanaInscrits(){
        System.out.println("Consulta NUMERO ESTUDIANTS INSCRITS");
 	PreparedStatement st = null;
 	ResultSet rs = null;
        Integer numTotatInscrits = 0;

        try{
        
            StringBuffer query = new StringBuffer();
            query.append("select count(*) numInscrits");
            query.append(" from inscripcio");
            query.append(" group by (idprova)");

            st = gestorBD.getConexion().prepareStatement(query.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);           
            rs = st.executeQuery();
            while (rs.next()) {
                Integer numIns = rs.getInt("numInscrits");
                numTotatInscrits = numTotatInscrits + numIns;
            }
            

        
        }catch (SQLException e) {
            System.out.println("Excepció SQL (funcio: getNumInscrits)");
 		e.printStackTrace();
                return null;
        
        }catch (Exception e){
            System.out.println("Excepció GENERAL (funcio: getNumInscrits)");
 		e.printStackTrace();
                return null;
        }
        
    return String.valueOf(numTotatInscrits);
        
    }*/
    
    
    public String getNumEstudiantsInscrits(){
        
        System.out.println("Consulta NUMERO ESTUDIANTS INSCRITS");
 	PreparedStatement st = null;
 	ResultSet rs = null;
        Integer numTotatInscrits = new Integer(0);

        try{
        
            StringBuffer query = new StringBuffer();
            query.append("select count(*) numInscrits");
            query.append(" from inscripcio");
            query.append(" group by (idprova)");

            st = gestorBD.getConexion().prepareStatement(query.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);           
            rs = st.executeQuery();
            while (rs.next()) {
                Integer numIns = new Integer(rs.getInt("numInscrits"));
                numTotatInscrits = new Integer(numTotatInscrits.intValue() + numIns.intValue());
            }
            

        
        }catch (SQLException e) {
            System.out.println("Excepció SQL (funcio: getNumInscrits)");
 		e.printStackTrace();
                return null;
        
        }catch (Exception e){
            System.out.println("Excepció GENERAL (funcio: getNumInscrits)");
 		e.printStackTrace();
                return null;
        }
        
    return String.valueOf(numTotatInscrits);
    }
    
    public String getPuntuacioMitjana(){
        
        System.out.println("Consulta PUNTUACIO MITJANA");
 	PreparedStatement st = null;
 	ResultSet rs = null;
        Float notaMitja = new Float(0.00);

        try{
        
            StringBuffer query = new StringBuffer();
            query.append("select avg(nota)");
            query.append("from participacio");
            query.append(" group by (idprova,tipusronda)");
            System.out.println(query);
            
            st = gestorBD.getConexion().prepareStatement(query.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);           
            rs = st.executeQuery();
            while (rs.next()) {
                
                notaMitja = new Float(notaMitja.floatValue() + rs.getFloat("avg"));
            }
            rs.last();
            Integer numParticipacions = new Integer(rs.getRow());
            notaMitja = new Float(notaMitja.floatValue() / numParticipacions.intValue());
            
            
        return String.valueOf(notaMitja);
        
        }catch (SQLException e) {
            System.out.println("Excepció SQL (funcio: getPuntuacioMitjana)");
 		e.printStackTrace();
                return null;
        
        }catch (Exception e){
            System.out.println("Excepció GENERAL (funcio: getPuntuacioMitjana)");
 		e.printStackTrace();
                return null;
        }
        
        
    }
  
    
}